#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
Created on 2014-11-5
@author: zhaoxiang
'''
import os
import math
import format_time
import DBConfig
from HandleMysql import *
import postUtils
import load_data
orderSource={2000:'客户提单',2005:'直销',2010:'渠道'}

#用户订单数据
def get_user_info(searchDate):
    sql=("select user_id,order_source,sum(first_payment) from t_common_order_info "
         "where product_id=100201 and order_state=45 "
         "and left(service_begin_time,10)<='%s' "
         "and left(service_end_time,10)>='%s' group by user_id " % (searchDate,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_26_data_app)
        handleMysql.connect()
        resultsDict = handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return resultsDict

#网灵通数
def get_vip_info(searchDate):
    sql=("select user_id,count(1) from t_common_order_info "
         "where order_state in (46,47,35,45,50) and product_id=100201 group by user_id")
    try:
        handleMysql = HandleMysql(DBConfig.db_26_data_app)
        handleMysql.connect()
        resultsDict = handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return resultsDict

#推广数
def get_promote_info(searchDate):
    month=format_time.get_month2(searchDate)
    sql=("select user_id,sum(promoting_count),sum(mannual_refresh_count+reserve_referesh_count) "
         "from promotion_info_%s where stat_date='%s' group by user_id " % (month,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_58_stats)
        handleMysql.connect()
        resultsDict = handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return resultsDict

#登录数
def get_login_info(searchDate):
    month=format_time.get_month2(searchDate)
    sql=("select user_id,sum(login_count) from user_action_%s where stat_date='%s' group by user_id" % (month,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_58_stats)
        handleMysql.connect()
        resultsDict = handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return resultsDict

#精准
def get_precise_info(searchDate):
    month=format_time.get_month2(searchDate)
    sql=("select user_id,count(1),sum(cost)/100 "
         "from t_jingzhun_user_stat_new_%s where stat_date='%s' group by user_id" % (month,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_26_data_app)
        handleMysql.connect()
        resultsDict = handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return resultsDict

#置顶
def get_top_info(searchDate):
    year=format_time.get_year(searchDate)
    sql=("select user_id,count(1),sum(consume)/100 "
         "from t_zhiding_consume_%s where stat_date='%s' group by user_id" % (year,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_26_data_app)
        handleMysql.connect()
        resultsDict = handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return resultsDict
#智能
def get_zhineng_info(searchDate):
    month=format_time.get_month2(searchDate)
    sql=("select user_id,count(1),sum(cost)/100 "
         "from t_zhineng_user_stat_new_%s where stat_date='%s'  group by user_id" % (month,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_26_data_app)
        handleMysql.connect()
        resultsDict = handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return resultsDict

#简历下载，面试邀请
def get_order_pkg(searchDate):
    month=format_time.get_month2(searchDate)
    sql=("select user_id,count(1),sum(pay_amount),sum(if(pkg_type!=1,1,0)),sum(if(pkg_type!=1,pay_amount,0)) "
         "from udp_buyResumePackage_detail_%s where stat_date='%s' and buy_resume_count>0 group by user_id" % (month,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_26_zhaopin)
        handleMysql.connect()
        resultsDict = handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return resultsDict

#发帖信息
def get_post_info(searchDate,userIdList):
    day=format_time.format_date(searchDate, '')
    count=int(math.ceil(len(userIdList)*1.0/10000))
    sendFinalRs={}
    showFinalRs={}
    try:
        handleMysql = HandleMysql(DBConfig.db_27_detail_info)
        handleMysql.connect()
        for i in range(count):
            print('%s begin search %s times' % (format_time.get_now(),i+1))
            idInStr=','.join(map(lambda uid:str(uid),userIdList[i*10000:(i+1)*10000]))
            if(len(idInStr) == 0):
                continue
            sql=("select info_uid,count(1),sum(if(state in(1,11),1,0)) "
                 "from d_common_info_detail_%s where stat_date='%s' and info_uid in(%s) group by info_uid" % (day,searchDate,idInStr) )
            resultsDict = handleMysql.get_as_dict(sql)
            sendFinalRs.update(resultsDict)
            sql=("select info_uid,sum(pv) "
                "from d_common_info_pv_%s where info_uid in(%s) group by info_uid" % (day,idInStr) )
            resultsDict = handleMysql.get_as_dict(sql)
            showFinalRs.update(resultsDict)
            print('%s end search %s times' % (format_time.get_now(),i+1))
    finally:
        handleMysql.close()
    return (sendFinalRs,showFinalRs)

#帖子
#def get_post_show_info(searchDate,userIdList):
#    day=format_time.format_date(searchDate, '')
#    count=int(math.ceil(len(userIdList)*1.0/10000))
#    finalRs={}
#    try:
#        handleMysql = HandleMysql(DBConfig.db_27_detail_info)
#        handleMysql.connect()
#        for i in range(count):
#            print('%s begin search %s times' % (format_time.get_now(),i+1))
#            idInStr=','.join(map(lambda uid:str(uid),userIdList[i*10000:(i+1)*10000]))
#            if(len(idInStr) == 0):
#                continue
#            sql=("select info_uid,sum(pv) "
#                "from d_common_info_pv_%s where info_uid in(%s) group by info_uid" % (day,idInStr) )
#            resultsDict = handleMysql.get_as_dict(sql)
#            finalRs.update(resultsDict)
#            print('%s end search %s times' % (format_time.get_now(),i+1))
#    except Exception as e:
#        sys.stderr.write(str(e))
#        return None
#    finally:
#        handleMysql.close()
#    return finalRs

creat_table_sql='''
    CREATE TABLE if not exists `t_zhaopin_vip_stat_%s` (
  `stat_date` date NOT NULL DEFAULT '0000-00-00' COMMENT '统计日期',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',
  `order_source` varchar(20) DEFAULT NULL COMMENT '订单来源',
  `vip_count` int(11) DEFAULT NULL COMMENT '网邻通次数',
  `post_count` int(11) DEFAULT NULL COMMENT '发帖次数',
  `valid_post_count` int(11) DEFAULT NULL COMMENT '有效发帖次数',
  `post_pv` int(11) DEFAULT NULL COMMENT '帖子浏览次数pv', 
  `promote_count` int(11) DEFAULT NULL COMMENT '推广次数',
  `refresh_count` int(11) DEFAULT NULL COMMENT '刷新次数',
  `login_count` int(11) DEFAULT NULL COMMENT '登录次数',
  `precise_count` int(11) DEFAULT NULL COMMENT '精准消费次数',
  `precise_amount` double DEFAULT NULL COMMENT '精准消费金额',
  `top_count` int(11) DEFAULT NULL COMMENT '置顶次数',
  `top_amount` double DEFAULT NULL COMMENT '置顶消费金额',
  `zhineng_count` int(11) DEFAULT NULL COMMENT '智能次数',
  `zhineng_amount` double DEFAULT NULL COMMENT '智能消费金额',
  `download_count` int(10) DEFAULT NULL COMMENT '简历下载次数',
  `download_amount` double DEFAULT NULL COMMENT '简历下载金额',
  `download_pkg_count` int(10) DEFAULT NULL COMMENT '简历下载套餐次数',
  `download_pkg_amount` double DEFAULT NULL COMMENT '简历下载套餐金额',
  `account_type` smallint DEFAULT NULL COMMENT '账户类型，0:字母账户，1:非0元账户'
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8 COMMENT='招聘会员数据';'''
def creat_table(month):
    sql = creat_table_sql % month
    try:
        handleMysql = HandleMysql(DBConfig.db_26_bi)
        handleMysql.connect()
        handleMysql.execute_sql(sql)
    except Exception as e:
        sys.stderr.write(str(e))
        return False
    finally:
        handleMysql.close()
    return True

def main(date):
    month=format_time.get_month2(date)
    day=format_time.format_date(date, '')
    fileName='./results/zhaopin_vip_stat_%s.txt' % day
    
    print("user_info")
    user_info = get_user_info(date)
    print("vip_info")
    vip_info = get_vip_info(date)
    print("promote_info")
    promote_info = get_promote_info(date)
    print("login_info")
    login_info = get_login_info(date)
    print("precise_info")
    precise_info = get_precise_info(date)
    print("top_info")
    top_info = get_top_info(date)
    print("zhineng_info")
    zhineng_info = get_zhineng_info(date)
    print("order_pkg info")
    order_pkg = get_order_pkg(date)
    print("post_info")
    post_send_info,post_show_info= get_post_info(date,user_info.keys())
#        print("post_show_info")
#        post_show_info = get_post_show_info(date,user_info.keys())
    print('have %s recodes !' % len(user_info))
    with open(fileName,'w') as f:
        for uid in user_info.keys():
            order_source = orderSource.get(user_info[uid][0],'其他')
            order_type =1 if  user_info[uid][1] > 0 else 0
            vip_count = vip_info.get(uid)[0] if uid in vip_info else 0;
            if uid in promote_info:
                promote_count = promote_info.get(uid)[0]
                refresh_count = promote_info.get(uid)[1]
            else :
                promote_count = 0
                refresh_count = 0
            login_count = login_info.get(uid)[0] if uid in login_info else 0
            precise_count = precise_info.get(uid)[0] if uid in precise_info else 0
            precise_amount = precise_info.get(uid)[1] if uid in precise_info else 0
            top_count = top_info.get(uid)[0] if uid in top_info else 0
            top_amount = top_info.get(uid)[1] if uid in top_info else 0
            zhineng_count = zhineng_info.get(uid)[0] if uid in zhineng_info else 0
            zhineng_amount = zhineng_info.get(uid)[1] if uid in zhineng_info else 0
            download_count = order_pkg.get(uid)[0] if uid in order_pkg else 0
            download_amount = order_pkg.get(uid)[1] if uid in order_pkg else 0
            download_pkg_count = order_pkg.get(uid)[2] if uid in order_pkg else 0
            download_pkg_amount = order_pkg.get(uid)[3] if uid in order_pkg else 0
            post_count = post_send_info.get(uid)[0] if uid in post_send_info else 0
            valid_post_count = post_send_info.get(uid)[1] if uid in post_send_info else 0
            post_pv = post_show_info.get(uid)[0] if uid in post_show_info else 0
            row=[date,uid,order_source,vip_count,post_count,valid_post_count,post_pv,promote_count,refresh_count,
                 login_count,precise_count,precise_amount,top_count,top_amount,zhineng_count,zhineng_amount,
                 download_count,download_amount,download_pkg_count,download_pkg_amount,order_type]
            f.write('\t'.join(map(lambda i : str(i), row))+'\n')
            
    sql = creat_table_sql % month
    try:
        handleMysql = HandleMysql(DBConfig.db_26_bi)
        handleMysql.connect()
        print('check table!')
        handleMysql.execute_sql(sql)
        load_data.load_data(handleMysql, os.path.abspath(fileName), 't_zhaopin_vip_stat_%s' % month, '\t')
    finally:
        handleMysql.close()
    return True
    
if __name__ == '__main__':
    if len(sys.argv) == 1:
        startDate=format_time.get_yesterday()
        endDate=format_time.get_yesterday()
    elif len(sys.argv) == 2:
        startDate=endDate=sys.argv[1]
    else:
        startDate=sys.argv[1]
        endDate=sys.argv[2]
    try:
        startDate=format_time.format_date(startDate, '-')
        endDate=format_time.format_date(endDate, '-')
        while startDate <= endDate:
            print('%s start deal %s' % (format_time.get_now(),startDate))
            main(startDate)
            print('%s finished %s' % (format_time.get_now(),startDate))
            startDate=format_time.var_date(startDate,sep='-', flag='+',interval=1)
    except Exception as e:
        sys.stderr.write('%s\n' % startDate +str(e))
    sys.exit(0)


#  mysql -h 10.5.20.26 -uhuangliang -pDcUsZkvcj8H8efPbpy -P5029 -D data_bi -e "SELECT * FROM t_zhaopin_vip_stat_201502 where stat_date not in('2015-02-05','2015-02-10');" > zp_vip.txt
#  mysql -h 10.5.20.26 -uhuangliang -pDcUsZkvcj8H8efPbpy -P5029 -D data_bi -e "load data local infile '/home/zhaoxiang/zp_user_report/zp_vip.txt' into table  t_zhaopin_vip_stat_201502 fields terminated by '\t' lines terminated by '\n'"